const express = require('express');
const pool = require('../util/pool');
const router = express.Router();

// multer
const multer = require('multer');
const uuid = require('uuid')
const storage = multer.diskStorage({
  //保存路径
  destination: function (req, file, cb) {
    cb(null, './public/uploads')
  },
  filename: function (req, file, cb) {
    let name = file.originalname
    let ext = name.substr(name.lastIndexOf('.'))
    cb(null, uuid.v4() + ext)
  }
})
const upload = multer({ storage: storage })
//图片接收请求
router.post('/upload',
  upload.array('uploadFile'), (req, res) => {
    console.log(req.files)
    let urls = []
    req.files.forEach(item => {
      urls.push(`http://localhost:3000/${item.filename}`)
    })
    res.send({ code: 200, msg: 'ok', urls })
  })

// 获取所有新闻的接口
router.get('/news', (req, res, next) => {
  // SQL语句以获取文章分类表的数据
  let sql = 'SELECT * FROM news ORDER BY id';
  // 执行SQL语句
  pool.query(sql, (err, result) => {
    if (err) {
      next(err);
      return;
    };
    if (result.length > 0) {
      res.send({ message: 'ok', code: 200, data: result });
    } else {
      res.send({ code: 301, msg: '查询失败' });
    };
  });
});

// 增加新闻接口
router.post('/addnews', (req, res, next) => {
  let { title, content, image, sign } = req.body;
  let sql = 'INSERT INTO news(title, content, image, sign) VALUES (?, ?, ?, ?)'
  let obj = [title, content, image, sign]
  pool.query(sql, obj, (err, result) => {
    if (err) {
      next(err);
      return;
    };
    res.send({
      code: 200,
      msg: '增加成功'
    });
  });
});

// 新闻删除接口
router.delete('/delete/:id', (req, res, next) => {
  let obj = req.params
  // console.log(obj);
  let sql = 'DELETE FROM news WHERE id = ?';
  pool.query(sql, [obj.id], (err, result) => {
    if (err) {
      next(err);
      return;
    };
    if (result.affectedRows > 0) {
      res.send({
        code: 200,
        msg: '删除成功'
      });
    } else {
      res.send({
        code: 301,
        msg: '删除失败'
      });
    };
  });
});

// 修改新闻接口


// 获取指定分类下包含文章数据的接口
router.get('/articles', (req, res) => {
  // 获取客户端传递的cid参数
  let cid = req.query.cid;

  // 获取客户端传递的page参数
  let page = req.query.page ? req.query.page : 1;

  // 存储每页显示的记录数
  let pagesize = 20;

  // 通过公式来计算从第几条记录开始返回
  let offset = (page - 1) * pagesize;

  // 用于存储获取到的总记录数
  let rowcount;

  // 获取指定分类下的文章总数
  let sql = 'SELECT COUNT(id) AS count FROM xzqa_article WHERE category_id=?';

  pool.query(sql, [cid], (error, results) => {
    if (error) throw error;
    // 将获取到总记录数赋给rowcount变量
    rowcount = results[0].count;
    /**************************************************/
    // 根据总记录数和每页显示的记录数来计算总页数
    let pagecount = Math.ceil(rowcount / pagesize);

    // 查询SQL语句
    sql = 'SELECT id,subject,description,image FROM xzqa_article WHERE category_id=? LIMIT ?,?';
    // 执行SQL
    pool.query(sql, [cid, offset, pagesize], (error, results) => {
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results, pagecount: pagecount });
    });

    /**************************************************/
  });

});

// 获取特定文章数据的接口
router.get('/detail', (req, res) => {
  //获取地址栏中的id参数
  let id = req.query.id;

  // SQL查询
  let sql = "SELECT r.id,subject,content,created_at,nickname,avatar,article_number FROM xzqa_article AS r INNER JOIN xzqa_author AS u ON author_id = u.id WHERE r.id=?";

  // 执行SQL查询
  pool.query(sql, [id], (error, results) => {
    if (error) throw error;
    // 返回数据到客户端
    res.send({ message: 'ok', code: 200, result: results[0] });
  });

});

//用户注册接口
router.post('/register', (req, res) => {
  //console.log(md5('12345678'));
  // 获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(id) AS count FROM xzqa_author WHERE username=?';
  pool.query(sql, [username], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT xzqa_author(username,password) VALUES(?,MD5(?))';
      pool.query(sql, [username, password], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});

// 用户登录接口
router.post('/login', (req, res) => {
  //获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  // SQL语句
  let sql = 'SELECT id,username,nickname,avatar FROM xzqa_author WHERE username=? AND password=MD5(?)';
  pool.query(sql, [username, password], (error, results) => {
    if (error) throw error;
    if (results.length == 0) { //登录失败
      res.send({ message: 'login failed', code: 201 });
    } else {                 //登录成功
      res.send({ message: 'ok', code: 200, result: results[0] });
    }
  });

});

module.exports = router;
